//Documentation complete
/**
 * @author Kazu
 * @version 2009MR30
 */

package broker;

import java.sql.*;
import java.util.*;
import java.util.Date;

import problemDomain.*;
import static problemDomain.ErrorBean.*;
import problemDomain.ErrorBean.ErrorCode;


/**
 * Database broker for cost vs. profit by menu item report.
 */
public class CostProfitByMenuItemReportBroker extends ReportBroker {

	/* Singleton Constructor */
	
	private static CostProfitByMenuItemReportBroker broker;
	
	/**
	 * Default constructor. Calls the openConnection method.
	 * @throws ErrorBean
	 */
	private CostProfitByMenuItemReportBroker () throws ErrorBean {
		reportSQL = null;
	}
	
	/**
	 * Retrieves the static FoodItemBroker instance.
	 * @return static FoodItemBroker
	 * @throws ErrorBean
	 */
	public synchronized static CostProfitByMenuItemReportBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new CostProfitByMenuItemReportBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private PreparedStatement reportSQL;
	private PreparedStatement getReportSQL () throws ErrorBean {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT vmis.menu_item_id id,`name`, SUM(vmis.cost * numsold) cost, SUM(vmis.price * numsold) sales_revenue,");
		sb.append(" SUM(vmis.price * numsold) - SUM(vmis.cost * numsold) gross_profit ");
		sb.append(" FROM v_menu_items_sold vmis join menu_item mi ON (mi.menu_item_id = vmis.menu_item_id) ");
		sb.append(" WHERE date_sold >= ? AND date_sold <= ? ");
		sb.append(" GROUP BY vmis.menu_item_id order by vmis.menu_item_id;");
		return prepare(reportSQL, sb.toString());
	}
	
	/* Prepared Statements */
	
	
	/* Methods */
	
	/**
	 * Produces the report.
	 * @param fromDate the date to lower bound the report
	 * @param toDate the date to upper bound the report
	 * @return a <code>List</code> of the results
	 * @throws ErrorBean if there is an error retrieving the data
	 */
	public List<MenuItemReport> getReport (Date fromDate, Date toDate) throws ErrorBean {
		try {
			PreparedStatement reportSQL = getReportSQL();
			List<MenuItemReport> list = new ArrayList<MenuItemReport>();
			reportSQL.setDate(1, new java.sql.Date(fromDate.getTime()));
			reportSQL.setDate(2, new java.sql.Date(toDate.getTime()));
			ResultSet results = reportSQL.executeQuery();
			
			MenuItemReport mir = null;
			while (results.next()) {
				int id = results.getInt("id");
				String name = results.getString("name");
				double cost = results.getDouble("cost");
				double sales_revenue = results.getDouble("sales_revenue");
				double gross_profit = results.getDouble("gross_profit");
				mir = new MenuItemReport(id, name, cost, sales_revenue, gross_profit);
				list.add(mir);
			}
			return list;
		} catch (SQLException e)
		{
			throwError(
					ErrorCode.BROKER_PREPARED_STATEMENTS_NOT_AVAILABLE,
					e.getMessage(),
					this);
			return null;
		}
	}
	
	/* END Methods */
	
}
